﻿Imports System.Data.Common
Imports System.Configuration
Imports System.Xml
Imports dooll.Config

Public Class DataFactory
    Private DConnection As DbConnection      '抽象类型
    Private DCommand As DbCommand            '抽象类型
    Private DProvider As DbProviderFactory   '提供
    Private mErrMessage As String
    Private mProviderName As String

    ''' <summary>
    ''' 创建
    ''' </summary>
    ''' <param name="ConnectionConfigName"></param>
    ''' <remarks></remarks>
    Sub New(Optional ByVal ConnectionConfigName As String = "default")
        CreateConnection(ConnectionConfigName)
    End Sub

    ''' <summary>
    ''' 错误信息
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ErrMessage() As String
        Get
            Return mErrMessage
        End Get
    End Property

    Public ReadOnly Property DProviderName As String
        Get
            Return mProviderName
        End Get
    End Property

    Public ReadOnly Property IsMsSql As Boolean
        Get
            Return LCase(mProviderName) = LCase("System.Data.SqlClient")
        End Get
    End Property

    Public ReadOnly Property IsOracle As Boolean
        Get
            Return LCase(mProviderName) = LCase("System.Data.OracleClient")
        End Get
    End Property

    ''' <summary>
    ''' 连接配置
    ''' </summary>
    ''' <param name="fKey"></param>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private ReadOnly Property ConnectionSettings(ByVal fKey As String) As ConnectionStringSettings
        Get
            If String.IsNullOrEmpty(fKey) OrElse ConfigurationManager.ConnectionStrings.Item(fKey) Is Nothing Then
                fKey = "default"
            End If

            If ConfigurationManager.ConnectionStrings(fKey) Is Nothing Then
                mErrMessage = String.Format("DataFactory Error [{0}] Not Exists .", fKey)
                Return Nothing
            Else
                Return ConfigurationManager.ConnectionStrings(fKey)
            End If
        End Get
    End Property

    ''' <summary>
    ''' 连接数据库
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Open()
        If DConnection.State = ConnectionState.Closed Then
            DConnection.Open()
        End If
    End Sub

    ''' <summary>
    ''' 关闭数据库连接
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Close()
        If Not DConnection Is Nothing Then
            If DConnection.State = ConnectionState.Open Then
                DConnection.Close()
            End If
        End If
    End Sub

    ''' <summary>
    ''' 创建连接
    ''' </summary>
    ''' <param name="ConnString"></param>
    ''' <remarks></remarks>
    Public Sub CreateConnection(ByVal ConnString As String)
        Try
            Dim ConnSet As ConnectionStringSettings = ConnectionSettings(ConnString)
            mProviderName = IIf(String.IsNullOrEmpty(ConnSet.ProviderName), "System.Data.SqlClient", ConnSet.ProviderName)
            DProvider = DbProviderFactories.GetFactory(mProviderName)
            DConnection = DProvider.CreateConnection()
            DConnection.ConnectionString = ConnSet.ConnectionString
        Catch ex As Exception
            mErrMessage = String.Format("Create Connection Error:", ex.Message)
            Console.WriteLine(mErrMessage)
        End Try
    End Sub

    ''' <summary>
    ''' 创建参数对象
    ''' </summary>
    ''' <returns>DbParameter</returns>
    ''' <remarks></remarks>
    Public Function CreateParameter() As DbParameter
        Try
            Return DProvider.CreateParameter()
        Catch ex As Exception
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
            Return Nothing
        End Try
    End Function


    ''' <summary>
    ''' 创建Command对象 存储过程方式
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数集</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDbCommand(ByVal ProcName As String, ByVal Params As DbParameter(), Optional ByVal isFunction As Boolean = False) As DbCommand
        Using Cmd As DbCommand = DProvider.CreateCommand()
            Try
                Cmd.Connection = DConnection
                Cmd.CommandText = ProcName
                Cmd.CommandType = CommandType.StoredProcedure
                REM 加载参数
                If Not Params Is Nothing Then
                    For Each SqlParam As DbParameter In Params
                        If Not SqlParam Is Nothing Then
                            If IsMsSql() AndAlso Not SqlParam.ParameterName.StartsWith("@") Then
                                SqlParam.ParameterName = String.Concat("@", SqlParam.ParameterName)
                            End If
                            Cmd.Parameters.Add(SqlParam)
                        End If

                    Next
                End If

                If isFunction Then
                    REM 增加默认的返回值参数
                    Dim ReturnValue As DbParameter = DProvider.CreateParameter()
                    ReturnValue.DbType = DbType.String
                    ReturnValue.Size = 1000
                    ReturnValue.ParameterName = "ReturnValue"
                    ReturnValue.Direction = ParameterDirection.ReturnValue

                    ReturnValue.Value = Nothing
                    Cmd.Parameters.Add(ReturnValue)
                End If

            Catch ex As Exception
                mErrMessage = String.Format("Can't Create Command:", ex.Message)
                Console.WriteLine(mErrMessage)
            End Try
            Return Cmd
        End Using
    End Function

    ''' <summary>
    ''' 创建Command对象 SQL 查询方式
    ''' </summary>
    ''' <param name="Sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDbCommand(ByVal Sql As String) As DbCommand
        Using Cmd As DbCommand = DProvider.CreateCommand()
            Cmd.Connection = DConnection
            Cmd.CommandType = CommandType.Text
            Cmd.CommandText = Sql
            Return Cmd
        End Using
    End Function


    ''' <summary>
    ''' 执行存储过程函数返回值
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数数组</param>
    ''' <returns>return 返回值</returns>
    ''' <remarks></remarks>
    Public Function ExecuteScalar(ByVal ProcName As String, ByVal Params As DbParameter(), ByRef ScalarValue As String) As Integer
        Dim ReturnValue As Integer
        Using SqlCmd As DbCommand = GetDbCommand(ProcName, Params, True)
            Try
                Open()
                ScalarValue = SqlCmd.ExecuteScalar()
                ReturnValue = CType(SqlCmd.Parameters("ReturnValue").Value, String)
                REM 关闭连接
                Close()
                SqlCmd.Dispose()
            Catch ex As Exception
                ReturnValue = -500
                mErrMessage = ex.Message
                Console.WriteLine(mErrMessage)
            End Try
        End Using
        Return ReturnValue
    End Function

    ''' <summary>
    ''' 执行带参数 XmlDocument 输入
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数组</param>
    ''' <param name="pXmlDoc">可返回 XmlDocument</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteProduce(ByVal ProcName As String, ByVal Params As DbParameter(), ByRef pXmlDoc As XmlDocument, Optional ByVal XmlRoot As String = "rows", Optional ByVal XmlElement As String = "row", Optional isFunction As Boolean = False) As Integer
        Dim ReturnValue As Integer
        Try
            Dim ds As New DataSet(XmlRoot)
            ReturnValue = ExecuteProduce(ProcName, Params, ds, {XmlElement}, isFunction)
            If Not ds Is Nothing Then
                pXmlDoc = New Xml.XmlDocument
                pXmlDoc.LoadXml(ds.GetXml())
            Else
                ReturnValue = -500
            End If
        Catch ex As Exception
            pXmlDoc = Nothing
            ReturnValue = -500
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try
        Return ReturnValue
    End Function

    ''' <summary>
    ''' 执行SQL语句，返回XML对象
    ''' </summary>
    ''' <param name="sql">SQL语句</param>
    ''' <param name="outPathDoc">可返回的XML对象</param>
    ''' <param name="XmlRoot">xml root</param>
    ''' <param name="XmlRow">xml 行节点</param>
    ''' <returns>返回的行数， </returns>
    ''' <remarks></remarks>
    Public Function ExecuteSelect(ByVal sql As String, ByRef outPathDoc As Xml.XPath.XPathDocument, Optional ByVal XmlRoot As String = "rows", Optional ByVal XmlRow As String = "row") As Integer
        Dim xmlString As String = String.Empty
        Dim ReturnValue As Integer = ExecuteSelect(sql, xmlString, XmlRoot, XmlRow)
        outPathDoc = New XPath.XPathDocument(New IO.StringReader(xmlString))
        Return ReturnValue
    End Function
    Public Function ExecuteSelect(ByVal sql As String, ByRef outXmlString As String, Optional ByVal XmlRoot As String = "rows", Optional ByVal XmlRow As String = "row") As Integer
        Dim ReturnValue As Integer = 0
        Try
            Using Cmd As DbCommand = GetDbCommand(sql)
                Dim ds As DataSet = New DataSet(XmlRoot)
                Open()
                Dim dDA As DbDataAdapter = DProvider.CreateDataAdapter
                dDA.SelectCommand = Cmd
                ReturnValue = dDA.Fill(ds, XmlRow)
                dDA.Dispose()
                dDA = Nothing
                Close()
                outXmlString = ds.GetXml()
                Console.WriteLine(outXmlString)
                ds = Nothing
            End Using
        Catch ex As Exception
            mErrMessage = ex.Message
            ReturnValue = -500
            Console.WriteLine(mErrMessage)
        End Try
        Return ReturnValue
    End Function

    ''' <summary>
    ''' 执行函数,返回 执行的结果 以（代码,信息方式）
    ''' </summary>
    ''' <param name="ProcName">函数</param>
    ''' <param name="params">参数</param> 
    ''' <returns>函数返回值</returns>
    ''' <remarks></remarks>
    Public Function ExecuteFunction(ByVal ProcName As String, ByVal params As DbParameter()) As String
        Dim ReturnValue As String = String.Empty
        If IsMsSql() Then
            Return ExecuteScalar(ProcName, params, ReturnValue)
        Else
            Try
                Using Cmd As DbCommand = GetDbCommand(ProcName, params, True)
                    Open()
                    Cmd.ExecuteNonQuery()
                    Close()
                    ReturnValue = CType(Cmd.Parameters("ReturnValue").Value, String).Trim()
                End Using
            Catch ex As Exception
                ReturnValue = String.Concat("-500,", ex.Message)
                Console.WriteLine(mErrMessage)
            End Try
        End If
        Return ReturnValue
    End Function


    ''' <summary>
    ''' 执行带参数 XPathDocument 输入
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数组</param>
    ''' <param name="outXPathDoc">可返回 XPathDocument</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteProduce(ByVal ProcName As String, ByVal Params As DbParameter(), ByRef outXPathDoc As XPath.XPathDocument, Optional ByVal isFunction As Boolean = False) As Integer
        Dim ReturnValue As Integer
        Try

            Select Case LCase(DProviderName)
                Case LCase("System.Data.SqlClient")

                    Using SqlCmd As SqlClient.SqlCommand = GetDbCommand(ProcName, Params, True)
                        Open()
                        outXPathDoc = New XPath.XPathDocument(SqlCmd.ExecuteXmlReader)
                        ReturnValue = CType(SqlCmd.Parameters("ReturnValue").Value, Integer)
                        Close()
                    End Using

                Case Else
                    Dim ds As New DataSet("rows")
                    ReturnValue = ExecuteProduce(ProcName, Params, ds, {"rows"}, isFunction)
                    outXPathDoc = New XPath.XPathDocument(New IO.StringReader(ds.GetXml()))
                    ds = Nothing

            End Select
        Catch ex As Exception
            outXPathDoc = Nothing
            ReturnValue = -500
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try
        Return ReturnValue
    End Function

    ''' <summary>
    ''' 执行带参数存储过程 返回DataSet
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数</param>
    ''' <param name="outDateSet">返回的DateSet</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Public Function ExecuteProduce(ByVal ProcName As String, ByVal Params As DbParameter(), ByVal outDateSet As DataSet, Optional ByVal FillTableName As String = "row", Optional ByVal isFunction As Boolean = False) As Integer
        Return ExecuteProduce(ProcName, Params, outDateSet, {FillTableName}, isFunction)
    End Function


    ''' <summary>
    ''' 执行带参数存储过程 返回DataSet
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="Params">参数</param>
    ''' <param name="outDateSet">返回的DateSet</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Public Function ExecuteProduce(ByVal ProcName As String, ByVal Params As DbParameter(), ByVal outDateSet As DataSet, ByVal FillTableName As String(), Optional ByVal isFunction As Boolean = False) As Integer
        Dim ReturnValue As Integer
        Try
            Using Cmd As DbCommand = GetDbCommand(ProcName, Params, isFunction)
                Open()
                Dim DR As System.Data.IDataReader = Cmd.ExecuteReader
                If Not DR Is Nothing Then
                    outDateSet.Load(DR, LoadOption.Upsert, FillTableName)
                Else
                    DR = Nothing
                End If

                Close()
                If isFunction Then
                    ReturnValue = CType(Cmd.Parameters("ReturnValue").Value, Integer)
                Else
                    ReturnValue = -500
                    mErrMessage = "非函数模式，无返回值"
                End If

            End Using
        Catch ex As Exception
            ReturnValue = -500
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try
        Return ReturnValue
    End Function

    ''' <summary>
    ''' 执行MSQL存储过程
    ''' </summary>
    ''' <param name="ProcName">存储过程名</param>
    ''' <param name="Params">参数数组</param>
    ''' <param name="outXPathDoc">返回的文档</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MsSqlExecuteProduce(ByVal ProcName As String, ByVal Params As DbParameter(), ByRef outXPathDoc As XPath.XPathDocument) As Integer
        Dim ReturnValue As Integer
        Try
            Using SqlCmd As SqlClient.SqlCommand = GetDbCommand(ProcName, Params)
                Open()

                outXPathDoc = New XPath.XPathDocument(SqlCmd.ExecuteXmlReader)
                ReturnValue = CType(SqlCmd.Parameters("ReturnValue").Value, Integer)

                '关闭连接
                Close()
            End Using
        Catch ex As Exception
            outXPathDoc = Nothing
            ReturnValue = -500
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try
    End Function

    ''' <summary>
    ''' 调用存储过程, 返回XPathDocument
    ''' </summary>
    ''' <param name="ProcName">存储过程</param>
    ''' <param name="ParamString">字符串数组</param>
    ''' <param name="pXPathDoc">Xml文档</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteProduce(ByVal ProcName As String, ByVal ParamString As String(), ByRef pXPathDoc As XPath.XPathDocument) As Integer
        Dim Params(ParamString.Length - 1) As DbParameter
        Try
            If ParamString.Length = 0 Then
                Params = Nothing
            Else
                For iLoop As Integer = 1 To ParamString.Length - 1
                    If Not String.IsNullOrEmpty(ParamString(iLoop)) AndAlso ParamString(iLoop).IndexOf("=") > 1 Then
                        Params(iLoop - 1) = DProvider.CreateParameter()
                        Params(iLoop - 1).ParameterName = ParamString(iLoop).Split(CChar("="))(0).Trim()
                        Params(iLoop - 1).Value = ParamString(iLoop).Split(CChar("="))(1).Trim()
                    End If
                Next
            End If
            Return ExecuteProduce(ProcName, Params, pXPathDoc)
        Catch ex As Exception
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try
    End Function

    ''' <summary>
    ''' Oracle查询数据表
    ''' </summary>
    ''' <param name="fnQueryTable">表名</param>
    ''' <param name="fnPageSize">页码</param>
    ''' <param name="fnPageCurrent">当前页</param>
    ''' <param name="fnFdShow">查询回显字段</param>
    ''' <param name="fnFdOrder">排序</param>
    ''' <param name="fnWhere">条件</param>
    ''' <param name="xPathDoc">XPath 对象</param>
    ''' <returns>记录总数(-500 数据库出错)</returns>
    ''' <remarks></remarks>
    Public Function OracleXQuery(ByVal fnQueryTable As String, ByVal fnPageSize As Integer, ByVal fnPageCurrent As Long, ByVal fnFdShow As String, ByVal fnFdOrder As String, ByVal fnWhere As String, ByRef xPathDoc As System.Xml.XPath.XPathDocument) As Long
        Dim ReturnValue As Integer = 0
        Try
            Dim Params(9) As OracleClient.OracleParameter
            Params(0) = New OracleClient.OracleParameter("p_tablename", fnQueryTable)
            Params(1) = New OracleClient.OracleParameter("p_tablecolumn", fnFdShow)
            Params(2) = New OracleClient.OracleParameter("p_order", fnFdOrder)
            Params(3) = New OracleClient.OracleParameter("p_pagesize", fnPageSize)
            Params(4) = New OracleClient.OracleParameter("p_curpage", fnPageCurrent)
            Params(5) = New OracleClient.OracleParameter("p_where", fnWhere)
            Params(6) = New OracleClient.OracleParameter("p_rowcount", 0)
            Params(6).Direction = ParameterDirection.Output
            Params(7) = New OracleClient.OracleParameter("p_pagecount", 0)
            Params(7).Direction = ParameterDirection.Output
            Params(8) = New OracleClient.OracleParameter("p_cursor", OracleClient.OracleType.Cursor)
            Params(8).Direction = ParameterDirection.Output
            Dim ds As New DataSet("rows")
            If ExecuteProduce("sp_page", Params, ds) = -500 Then
                Return -500
            End If
            xPathDoc = New XPath.XPathDocument(New IO.StringReader(ds.GetXml))
            ReturnValue = IIf(Params(6) Is Nothing, 0, CLng(Params(6).Value))
        Catch ex As Exception
            ReturnValue = -500
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
        End Try

        Return ReturnValue
    End Function

    ''' <summary>
    ''' MsSqlQuery
    ''' </summary>
    ''' <param name="fnQueryTable"></param>
    ''' <param name="fnPageSize"></param>
    ''' <param name="fnPageCurrent"></param>
    ''' <param name="fnFdShow"></param>
    ''' <param name="fnFdOrder"></param>
    ''' <param name="fnWhere"></param>
    ''' <param name="xPathDoc"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MsSqlQuery(ByVal fnQueryTable As String, ByVal fnPageSize As Integer, ByVal fnPageCurrent As Long, ByVal fnFdShow As String, ByVal fnFdOrder As String, ByVal fnWhere As String, ByRef xPathDoc As System.Xml.XPath.XPathDocument) As Long
        Dim Params(6) As SqlClient.SqlParameter
        Params(0) = New SqlClient.SqlParameter("@RecordCount", SqlDbType.Int, 8, ParameterDirection.Output, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, 0)
        Params(1) = New SqlClient.SqlParameter("@QueryStr", SqlDbType.NVarChar, 1000, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnQueryTable.ToString)
        Params(2) = New SqlClient.SqlParameter("@PageSize", SqlDbType.Int, 8, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnPageSize)
        Params(3) = New SqlClient.SqlParameter("@PageCurrent", SqlDbType.Int, 8, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnPageCurrent)
        Params(4) = New SqlClient.SqlParameter("@FdShow", SqlDbType.NVarChar, 1000, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnFdShow.ToString)
        Params(5) = New SqlClient.SqlParameter("@FdOrder", SqlDbType.NVarChar, 1000, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnFdOrder.ToString)
        Params(6) = New SqlClient.SqlParameter("@FdWhere", SqlDbType.NVarChar, 1000, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, fnWhere)
        If ExecuteProduce("[sp_XmlSelect]", Params, xPathDoc) = -500 Then
            Return -500
        End If
        Return IIf(Params(0) Is Nothing, 0, CLng(Params(0).Value))
    End Function


    ''' <summary>
    ''' 页面的功通
    ''' </summary>
    ''' <param name="fnQueryTable"></param>
    ''' <param name="fnPageSize"></param>
    ''' <param name="fnPageCurrent"></param>
    ''' <param name="fnFdShow"></param>
    ''' <param name="fnFdOrder"></param>
    ''' <param name="fnWhere"></param>
    ''' <param name="xPathDoc"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function XQuery(ByVal fnQueryTable As String, ByVal fnPageSize As Integer, ByVal fnPageCurrent As Long, ByVal fnFdShow As String, ByVal fnFdOrder As String, ByVal fnWhere As String, ByRef xPathDoc As System.Xml.XPath.XPathDocument) As Long
        Select Case True
            Case IsOracle
                Return OracleXQuery(fnQueryTable, fnPageSize, fnPageCurrent, fnFdShow, fnFdOrder, fnWhere, xPathDoc)
            Case IsMsSql
                Return MsSqlQuery(fnQueryTable, fnPageSize, fnPageCurrent, fnFdShow, fnFdOrder, fnWhere, xPathDoc)
        End Select
    End Function


    Public Function XQuery(ByVal label As LabelTable, ByVal xpdoc As Xml.XPath.XPathDocument) As Integer
        Select Case label.type
            Case "proc"
                Return ExecuteProduce(label.Name, ConvertParam(label.Param), xpdoc)
            Case Else
                Return XQuery(label.Name, label.MaxOnPage, label.CurPage, label.Query, label.OrderBy, ConvertParam(label.Where), xpdoc)
        End Select

    End Function

    ''' <summary>
    ''' 转换参数
    ''' </summary>
    ''' <param name="params">label参数数组</param>
    ''' <returns>DbParames</returns>
    ''' <remarks></remarks>
    Public Function ConvertParam(ByVal params As ParamItem()) As DbParameter()

        If params Is Nothing OrElse params.Length = 0 Then
            Return Nothing
        End If

        Dim arrParams(params.Length) As DbParameter

        Dim it As ParamItem
        Dim val As String = String.Empty
        For i As Integer = 0 To params.Length - 1
            it = params(i)
            arrParams(i) = CreateParameter()
            arrParams(i).ParameterName = it.field
            arrParams(i).Value = val
        Next

        Return arrParams
    End Function


    ''' <summary>
    ''' 转换参数
    ''' </summary>
    ''' <param name="ParamString">存储过程</param>
    ''' <param name="startIndex">字符串数组(a=1,b=2,c=3...)</param>
    ''' <returns>DbParameter数组</returns>
    ''' <remarks></remarks>
    Public Function ConvertParam(ByVal ParamString As String(), Optional ByVal startIndex As Integer = 0) As DbParameter()
        Dim Params(ParamString.Length - 1) As DbParameter
        Try
            If ParamString.Length = 0 Then
                Params = Nothing
            Else
                For iLoop As Integer = startIndex To ParamString.Length - 1
                    If Not String.IsNullOrEmpty(ParamString(iLoop)) AndAlso ParamString(iLoop).IndexOf("=") > 1 Then
                        Params(iLoop) = DProvider.CreateParameter()
                        Params(iLoop).ParameterName = ParamString(iLoop).Split(CChar("="))(0).Trim()
                        Params(iLoop).Value = ParamString(iLoop).Split(CChar("="))(1).Trim()
                    End If
                Next
            End If
        Catch ex As Exception
            mErrMessage = ex.Message
            Console.WriteLine(mErrMessage)
            Params = Nothing
        End Try
        Return Params
    End Function

    ''' <summary>
    ''' 转换参数
    ''' </summary>
    ''' <param name="wi"></param>
    ''' <returns>where string</returns>
    ''' <remarks></remarks>
    Public Function ConvertParam(ByVal wi As WhereItem()) As String
        Dim s As String = String.Empty
        If Not wi Is Nothing Then

            For Each item As WhereItem In wi
                If item.mode.ToLower = "between" Then
                    Dim MinValue As String = item.MinValue
                    Dim MaxValue As String = item.MaxValue
                    s = String.Concat(s, " ", item.andor, " ", item.field, " ", item.mode, " ", MinValue, " and ", MaxValue)
                Else

                    Dim itemValue As String = item.Value

                    If Not String.IsNullOrEmpty(itemValue) Then

                        If item.mode.ToLower = "like" Then
                            itemValue = String.Format(item.pattern, itemValue)
                        End If

                        s = String.Concat(s, " ", item.andor, IIf(item.before = 1, " (", " "), item.field, " ", item.mode, String.Format(item.format, itemValue), IIf(item.after = 1, ")", ""))

                    End If
                End If

            Next
        End If

        If Not String.IsNullOrEmpty(s) Then s = String.Concat("1=1 ", s)
        Return s

    End Function

    Protected Overrides Sub Finalize()
        DConnection = Nothing
        MyBase.Finalize()
    End Sub
End Class

